package com.chauncy.DMS.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import util.List2Json1.List2Json1;
import util.c3p0.C3p0Utils;

import com.chauncy.DMS.bean.DormInfoBean;
import com.chauncy.DMS.bean.ManagerInfoBean;
import com.chauncy.DMS.bean.StuDormInfoBean;
import com.sun.org.apache.bcel.internal.generic.NEW;

public class ManagerDaoImplement implements ManagerDao {

	public List<ManagerInfoBean> managerLogin(ManagerInfoBean miBean)
			throws SQLException {
		//获取queryRunner
		QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select * from manager_info_table " +
				"WHERE ( Emp_no = ? or Manager_name = ? " +
				") AND Manager_pwd = ?;";
		return queryRunner.query(sql, 
				new BeanListHandler<ManagerInfoBean>(ManagerInfoBean.class),
				miBean.getEmp_no(),miBean.getManager_name(),miBean.getManager_pwd());
	}
	/**
	 * 实现分页已知的方法中必须获取三个参数
	 *  a.limit  页面显示数据条目数
	 *  b.page  当前页码
	 *  c.countItem 数据总数
	 *  limit page layui开启page之后会默认传递这两个参数，问题就是获取countItem 
	 *  写一下layui table 分页实现思路：table开启分页之后会默认传递page limit 两个参数，你在后台获取
	 *  String sql = "select * from table limit ((page - 1)*limit),limit;"
	 *  然后获取需要显示数据的总条目数，问题就在这，上面的sql只会显示limit条数据，而不是全部数据
	 *  所以只能通过count(*) 查询"select * from table" 获取全部数据条目数
	 *  。。。。。。。
	 */
	public List<DormInfoBean> selectDormPeople(String Huildings,int page,int limit)
			throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select Dorm_number,Checkin_number,Actual_number_of_people from dorm_info_table WHERE Checkin_number > Actual_number_of_people AND Buildings_number =  ? LIMIT ?,?;";
		return queryRunner.query(sql, new BeanListHandler<DormInfoBean>(DormInfoBean.class),Huildings,((page-1)*limit),limit);
	}

	public List<StuDormInfoBean> selectSDIByNameAndNumber(String sName,
			String sNumber) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select Stu_name,College,Study_major,The_Class,Dorm_number from stu_dorm_info_table WHERE Stu_name = ? AND Stu_number = ?;";
		return queryRunner.query(sql,new BeanListHandler<StuDormInfoBean>(StuDormInfoBean.class),sName,sNumber);
	}
	
	public Long count(String sql, Object[] param) {
		
		QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
		Long count = Long.valueOf(0L);
		//	System.out.println(param +"param.length:"+param.length);
		try {
				count = (Long) queryRunner.query(sql, new ScalarHandler(), param);	
				System.out.println(count);
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		//	System.out.println("countItem:::"+sql);
		return count;
	}
	
	
	
	public List<Map<String, Object>> query(String sql, Object[] param) {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
		try {
			//System.out.println("querySQL:"+sql);
				return queryRunner.query(sql, new MapListHandler(),param);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public List<Map<String, Object>> query(String sql) {
		// TODO Auto-generated method stub
		QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
		try {
			//System.out.println("querySQL:"+sql);
				return queryRunner.query(sql, new MapListHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public int updata(String sql, Object[] param) {
		// TODO Auto-generated method stub
		Connection connection = C3p0Utils.getConnection();
		//开启事务
		C3p0Utils.startTransaction();
		QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
		int flag = 0;
		try {
			//执行更新
			flag = queryRunner.update(sql,param);
			//提交事务
			C3p0Utils.commit();
		} catch (SQLException e) {
			//回滚
			C3p0Utils.rollback();
			e.printStackTrace();
			
		}finally{
			//释放连接
			C3p0Utils.releaseConnection();
		}
		return flag;
	}
	
}
